package com.qf.dao.impl;

import com.qf.dao.GoodsDao;
import com.qf.pojo.TbGoods;
import com.qf.pojo.TbGoodsKz;
import com.qf.utils.DBUtils;
import com.qf.utils.SqlUtils;
import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.MapHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;
import java.util.Map;

public class GoodsDaoImpl implements GoodsDao {
    QueryRunner queryRunner = new QueryRunner();

    @Override
    public long addGoodsKz(TbGoodsKz tbGoodsKz) throws SQLException {
        String sql = SqlUtils.getSql("goodskz");
        long id = queryRunner.insert(DBUtils.getConnection(),sql, new ScalarHandler<>(), tbGoodsKz.getName(), tbGoodsKz.getPubdate(), tbGoodsKz.getPicture(), tbGoodsKz.getPrice(), tbGoodsKz.getStar(), tbGoodsKz.getIntro(), tbGoodsKz.getTypeid());
        return id;
    }

    @Override
    public long addGoods(long kzid) throws SQLException {
        String sql = SqlUtils.getSql("goods");
        long update = queryRunner.insert(DBUtils.getConnection(), sql, new ScalarHandler<>(), kzid);
        return update;
    }

    @Override
    public int getGoodsCountByTypeId(int typeId) {
        try {
            String sql = "SELECT count(*) count from tb_goods tg LEFT JOIN tb_goods_kz tgz on tg.kzid =tgz.id  where typeid  =?";
            Map<String, Object> map = queryRunner.query(DBUtils.getConnection(),sql, new MapHandler(), typeId);
            //System.err.println(map);
            return ((Long) map.get("count")).intValue();
        } catch (Exception e) {
            e.printStackTrace();
        }
        return 0;
    }

    @Override
    public List<TbGoods> getGoodsByTypeIdAndLimit(int typeid, int start, int rows) {
        try {
//            String sql = "SELECT  * FROM tb_goods WHERE typeid =? limit ?,?";
            //快照表的 id 不能返回,因为会被当做商品的 id 封装到对象中,所以我们返回了部分字段
            //解决方式 2, 将快照表的 id 变个名字
            String sql = "SELECT tg.*,tgz.name,tgz.pubdate,tgz.picture,tgz.price,tgz.star ,tgz.intro,tgz.typeid from tb_goods tg LEFT JOIN tb_goods_kz tgz on tg.kzid =tgz.id  where typeid =? limit ?,?";
            List<TbGoods> goodsList = queryRunner.query(DBUtils.getConnection(),sql, new BeanListHandler<>(TbGoods.class), typeid, start, rows);
            return goodsList;
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return new ArrayList<>();
    }

    @Override
    public TbGoods getGoodById(int id) {
        TbGoods goods = null;
        try {
            String sql = "SELECT  tg.*,tgz.name,tgz.pubdate,tgz.picture,tgz.price,tgz.star ,tgz.intro,tgz.typeid FROM tb_goods tg LEFT JOIN tb_goods_kz tgz on tg.kzid =tgz.id  WHERE tg.id=?";
            goods = queryRunner.query(DBUtils.getConnection(),sql, new BeanHandler<>(TbGoods.class), id);
        } catch (SQLException throwables) {
            throwables.printStackTrace();
        }
        return goods;

    }
}
